using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
using System.ComponentModel;
/*
 * Autor      : IT-Developer1
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
    [DataObject(true)]
    public class CAcoesProgramas
    {
        private string IDT_ACAO;
        //private string IDT_PROG ;
        private string IDT_TPO_ACAO;
        private string DSC_ACAO;

        /// <sumary>
        /// Construtor da classe acoes_programas
        /// <sumary>
        public CAcoesProgramas()
        {

        }

        /// <sumary>
        /// Construtor da classe Acoes_programas
        /// <sumary>
        public CAcoesProgramas(string IDT_ACAO, string IDT_TPO_ACAO, string DSC_ACAO)
        {
            this.IDT_ACAO = IDT_ACAO;
            //this.IDT_PROG = IDT_PROG;
            this.IDT_TPO_ACAO = IDT_TPO_ACAO;
            this.DSC_ACAO = DSC_ACAO;
        }

        #region Propriedades
        /// <sumary>
        /// Identificador da acao
        /// <sumary>
        public string Idt_acao
        {
            get
            {
                return this.IDT_ACAO;
            }
            set
            {
                this.IDT_ACAO = value;
            }
        }
        /// <sumary>
        /// Identificador do programa
        /// <sumary>
        //public string Idt_prog 
        //{
        //   get
        //   {
        //       return this.IDT_PROG;
        //   }
        //   set
        //   {
        //       this.IDT_PROG = value;
        //   }
        //}
        /// <sumary>
        /// Identificador do tipo de acao
        /// <sumary>
        public string Idt_tpo_acao
        {
            get
            {
                return this.IDT_TPO_ACAO;
            }
            set
            {
                this.IDT_TPO_ACAO = value;
            }
        }
        /// <sumary>
        /// Descricao da acao
        /// <sumary>
        public string Dsc_acao
        {
            get
            {
                return this.DSC_ACAO;
            }
            set
            {
                this.DSC_ACAO = value;
            }
        }
        #endregion Propriedades

        /// <summary>
        /// Inserindo um novo Acoes_programas.
        /// </summary>
        public void Inserir(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_ACOES_PROGRAMAS
			                  ( IDT_ACAO, IDT_TPO_ACAO, DSC_ACAO )
						   values
						      ( (select max(IDT_ACAO) from Serix.SRX_ACOES_PROGRAMAS)+1
                                ,:IDT_TPO_ACAO, :DSC_ACAO )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                //command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_PROG",OracleType.Number)); 
                command.Parameters.Add(new OracleParameter("IDT_TPO_ACAO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("DSC_ACAO", OracleType.VarChar));

                //command.Parameters["IDT_ACAO"].Value = this.IDT_ACAO;
                //command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_TPO_ACAO"].Value = this.IDT_TPO_ACAO;
                command.Parameters["DSC_ACAO"].Value = this.DSC_ACAO;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// Alterando um Acoes_programas.
        /// </summary>
        public void Alterar(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_ACOES_PROGRAMAS set 
			                   IDT_TPO_ACAO = :IDT_TPO_ACAO, DSC_ACAO = :DSC_ACAO
						   where
						       IDT_ACAO = :IDT_ACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                //command.Parameters.Add(new OracleParameter("IDT_PROG",OracleType.Number)); 
                command.Parameters.Add(new OracleParameter("IDT_TPO_ACAO", OracleType.Number));
                command.Parameters.Add(new OracleParameter("DSC_ACAO", OracleType.VarChar));

                command.Parameters["IDT_ACAO"].Value = this.IDT_ACAO;
                //command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_TPO_ACAO"].Value = this.IDT_TPO_ACAO;
                command.Parameters["DSC_ACAO"].Value = this.DSC_ACAO;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// Exclui um  Acoes_programas.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_ACOES_PROGRAMAS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_ACAO", DbType.Int32, this.IDT_ACAO));
            tab.delete();
        }
        [System.ComponentModel.DataObjectMethod(DataObjectMethodType.Select)]
        static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_ACOES_PROGRAMAS order by DSC_ACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public CAcoesProgramas PesquisaporCodigo(string idt_acao, string login)
        {
            CAcoesProgramas obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_ACOES_PROGRAMAS 
                            WHERE IDT_ACAO = :IDT_ACAO";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":IDT_ACAO", DbType.String, idt_acao);

                qy.Executar();
                obj = new CAcoesProgramas(qy.ObterCampo("IDT_ACAO"), qy.ObterCampo("IDT_TPO_ACAO"), qy.ObterCampo("DSC_ACAO"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CAcoesProgramas();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_ACAO() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.Executar();
                return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return -1;
            }
            finally
            {
                qy.FecharConexao();
            }
        }

        [DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static DataTable ObterAcoesPrograma(string idt_prog, string ano_ais, string login)
        {
            if (string.IsNullOrEmpty(idt_prog) || string.IsNullOrEmpty(ano_ais))
                return new DataTable();
            string sql = @"
select distinct acao.idt_acao, acao.dsc_acao from Srx_Acoes_Indicadores ais 
  join srx_acoes_programas acao on acao.idt_acao = ais.idt_acao
where idt_prog = :idt_prog and ano_AIS = :ano_ais";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ano_ais", OracleType.Number));
                command.Parameters["idt_prog"].Value = idt_prog;
                command.Parameters["ano_ais"].Value = ano_ais;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }

        static public DataTable ObterPorIDT_PROG(string idt_prog)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_ACOES_PROGRAMAS  where IDT_PROG = :IDT_PROG order by DSC_ACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters["IDT_PROG"].Value = idt_prog;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }
        static public DataTable ObterPorIDT_TPO_ACAO(string idt_tpo_acao)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_ACOES_PROGRAMAS  where IDT_TPO_ACAO = :IDT_TPO_ACAO order by DSC_ACAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TPO_ACAO", OracleType.Number));
                command.Parameters["IDT_TPO_ACAO"].Value = idt_tpo_acao;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}

